USE [master]
GO
CREATE DATABASE [PMURegistry]
GO

ALTER DATABASE [PMURegistry] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [PMURegistry] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [PMURegistry] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [PMURegistry] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [PMURegistry] SET ARITHABORT OFF 
GO
ALTER DATABASE [PMURegistry] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [PMURegistry] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [PMURegistry] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [PMURegistry] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [PMURegistry] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [PMURegistry] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [PMURegistry] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [PMURegistry] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [PMURegistry] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [PMURegistry] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [PMURegistry] SET  ENABLE_BROKER 
GO
ALTER DATABASE [PMURegistry] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [PMURegistry] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [PMURegistry] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [PMURegistry] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [PMURegistry] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [PMURegistry] SET  READ_WRITE 
GO
ALTER DATABASE [PMURegistry] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [PMURegistry] SET  MULTI_USER 
GO
ALTER DATABASE [PMURegistry] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [PMURegistry] SET DB_CHAINING OFF 
GO
-- The next three commented statements are used to create a user with access to the PMURegistry database.
-- Be sure to change the username and password.
-- Replace-all from NewUser to the desired username is the preferred method of changing the username.

--IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NewUser')
--CREATE LOGIN [NewUser] WITH PASSWORD=N'MyPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
--GO

USE [PMURegistry]
GO

--CREATE USER [NewUser] FOR LOGIN [NewUser]
--GO
CREATE ROLE [PmuRegistryWebRole] AUTHORIZATION [dbo]
GO
--EXEC sp_addrolemember N'PmuRegistryWebRole', N'NewUser'
--GO
EXEC sp_addrolemember N'db_datareader', N'PmuRegistryWebRole'
GO
EXEC sp_addrolemember N'db_datawriter', N'PmuRegistryWebRole'
GO

/****** Object:  Table [dbo].[FundingSource]    Script Date: 05/12/2010 14:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FundingSource](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SourceName] [varchar](256) NOT NULL,
 CONSTRAINT [PK_FundingSource] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Protocol]    Script Date: 05/12/2010 14:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Protocol](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Acronym] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Protocol] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SignalType]    Script Date: 05/12/2010 14:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SignalType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Acronym] [nvarchar](4) NOT NULL,
	[Suffix] [nvarchar](2) NOT NULL,
	[Abbreviation] [nvarchar](2) NOT NULL,
	[Source] [nvarchar](10) NOT NULL,
	[EngineeringUnits] [nvarchar](10) NULL,
 CONSTRAINT [PK_SignalType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Vendor]    Script Date: 05/12/2010 14:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Vendor](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Acronym] [nvarchar](3) NULL,
	[Name] [nvarchar](100) NOT NULL,
	[PhoneNumber] [nvarchar](100) NULL,
	[ContactEmail] [nvarchar](100) NULL,
	[URL] [nvarchar](max) NULL,
 CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Interconnection]    Script Date: 05/12/2010 14:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Interconnection](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Acronym] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Interconnection] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[NercRegion]    Script Date: 05/12/2010 14:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NercRegion](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](64) NOT NULL,
	[Description] [varchar](256) NULL,
 CONSTRAINT [PK_NercRegion] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DeviceStatus]    Script Date: 05/12/2010 14:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DeviceStatus](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](32) NOT NULL,
	[Description] [varchar](512) NULL,
 CONSTRAINT [PK_DeviceStatus] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ContactType]    Script Date: 05/12/2010 14:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContactType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](64) NOT NULL,
	[Description] [varchar](512) NULL,
 CONSTRAINT [PK_ContactType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[User]    Script Date: 05/12/2010 14:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](64) NOT NULL,
	[Password] [nvarchar](32) NOT NULL,
	[FirstName] [nvarchar](64) NOT NULL,
	[LastName] [nvarchar](64) NOT NULL,
	[Email] [nvarchar](128) NOT NULL,
	[Organization] [nvarchar](128) NULL,
	[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_User_CreatedOn]  DEFAULT (getdate()),
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserComment]    Script Date: 05/12/2010 14:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserComment](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](64) NOT NULL,
	[Subject] [nvarchar](128) NOT NULL,
	[Comment] [nvarchar](1024) NOT NULL,
	[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_UserComment_CreatedOn]  DEFAULT (getdate()),
 CONSTRAINT [PK_UserComment] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ErrorLog]    Script Date: 05/12/2010 14:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Source] [varchar](256) NOT NULL,
	[Message] [varchar](1024) NOT NULL,
	[Detail] [varchar](max) NULL,
	[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_CreatedOn]  DEFAULT (getdate()),
 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[NetworkModel]    Script Date: 05/12/2010 14:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NetworkModel](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NetworkElementUID] [varchar](256) NOT NULL,
	[LineBusName] [varchar](512) NOT NULL,
	[RegionalLineBusID] [varchar](256) NOT NULL,
	[LocalLineBusID] [varchar](256) NOT NULL,
	[ExtraLineBusID] [varchar](256) NULL,
	[NominalKv] [int] NULL,
 CONSTRAINT [PK_NetworkModel] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Device]    Script Date: 05/12/2010 14:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Device](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Acronym] [nvarchar](16) NOT NULL,
	[Name] [nvarchar](128) NOT NULL,
	[SubstationAcronym] [varchar](32) NOT NULL,
	[SubstationName] [varchar](512) NULL,
	[Longitude] [decimal](9, 6) NULL CONSTRAINT [DF_Device_Longitude]  DEFAULT ((0)),
	[Latitude] [decimal](9, 6) NULL CONSTRAINT [DF_Device_Latitude]  DEFAULT ((0)),
	[TimeZone] [varchar](128) NULL,
	[InterconnectionID] [int] NOT NULL,
	[CompanyID] [int] NOT NULL,
	[VendorDeviceID] [int] NULL,
	[ProtocolID] [int] NULL,
	[MeasuredLines] [int] NOT NULL CONSTRAINT [DF_Device_MeasuredLines]  DEFAULT ((1)),
	[IsConcentrator] [bit] NOT NULL CONSTRAINT [DF_Device_IsConcentrator]  DEFAULT ('0'),
	[ParentID] [int] NULL,
	[StatusID] [int] NOT NULL,
	[StatusDate] [datetime] NOT NULL CONSTRAINT [DF_Device_StatusDate]  DEFAULT (getdate()),
	[TechnicalContactID] [int] NULL,
	[InServiceDate] [datetime] NULL,
	[FundingSourceID] [int] NULL,
	[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Device_CreatedOn]  DEFAULT (getdate()),
	[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_Device_UpdatedOn]  DEFAULT (getdate()),
 CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Measurement]    Script Date: 05/12/2010 14:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Measurement](
	[SignalID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Measurement_SignalID]  DEFAULT (newid()),
	[DeviceID] [int] NOT NULL,
	[PointTag] [nvarchar](50) NOT NULL,
	[AlternateTag] [nvarchar](50) NULL,
	[SignalTypeID] [int] NOT NULL,
	[PhasorSourceIndex] [int] NULL,
	[SignalReference] [nvarchar](max) NOT NULL,
	[Adder] [float] NOT NULL CONSTRAINT [DF_Measurement_Adder]  DEFAULT ((0.0)),
	[Multiplier] [float] NOT NULL CONSTRAINT [DF_Measurement_Multiplier]  DEFAULT ((1.0)),
	[Description] [nvarchar](max) NULL,
	[Enabled] [bit] NOT NULL CONSTRAINT [DF_Measurement_Enabled]  DEFAULT ((0)),
 CONSTRAINT [PK_Measurement] PRIMARY KEY CLUSTERED 
(
	[SignalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[VendorDevice]    Script Date: 05/12/2010 14:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VendorDevice](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VendorID] [int] NOT NULL CONSTRAINT [DF_VendorDevice_VendorID]  DEFAULT ((10)),
	[Name] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[URL] [nvarchar](max) NULL,
 CONSTRAINT [PK_VendorDevice] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Company]    Script Date: 05/12/2010 14:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Company](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Acronym] [nvarchar](50) NOT NULL,
	[MapAcronym] [nchar](3) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[URL] [nvarchar](max) NULL,
	[InterconnectionID] [int] NOT NULL,
	[NercRegionID] [int] NOT NULL,
	[TimeZone] [varchar](64) NOT NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Contact]    Script Date: 05/12/2010 14:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contact](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CompanyID] [int] NOT NULL,
	[ContactTypeID] [int] NOT NULL,
	[FirstName] [varchar](64) NOT NULL,
	[LastName] [varchar](64) NOT NULL,
	[Title] [varchar](256) NULL,
	[Email] [varchar](256) NOT NULL,
	[Phone] [varchar](32) NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Phasor]    Script Date: 05/12/2010 14:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Phasor](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DeviceID] [int] NOT NULL,
	[Label] [nvarchar](50) NOT NULL,
	[Type] [nchar](1) NOT NULL CONSTRAINT [DF_Phasor_Type]  DEFAULT (N'V'),
	[Phase] [nchar](1) NOT NULL CONSTRAINT [DF_Phasor_Phase]  DEFAULT (N'+'),
	[DestinationPhasorID] [int] NULL,
	[SourceIndex] [int] NOT NULL CONSTRAINT [DF_Phasor_SourceIndex]  DEFAULT ((0)),
	[NetworkModelID] [int] NULL,
 CONSTRAINT [PK_Phasor] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[MeasurementDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MeasurementDetail]
AS
SELECT     dbo.Device.CompanyID, dbo.Company.Acronym AS CompanyAcronym, dbo.Company.Name AS CompanyName, dbo.Measurement.SignalID, 
                      dbo.Measurement.PointTag, dbo.Measurement.AlternateTag, dbo.Measurement.DeviceID, 
                      dbo.Device.Acronym AS DeviceAcronym, dbo.Device.Name AS DeviceName,  
                      dbo.Device.VendorDeviceID, dbo.VendorDevice.Name AS VendorDeviceName, dbo.VendorDevice.Description AS VendorDeviceDescription, 
                      dbo.Device.ProtocolID, dbo.Protocol.Acronym AS ProtocolAcronym, dbo.Protocol.Name AS ProtocolName, dbo.Measurement.SignalTypeID, 
                      dbo.Measurement.PhasorSourceIndex, dbo.Phasor.Label AS PhasorLabel, dbo.Phasor.Type AS PhasorType, dbo.Phasor.Phase, 
                      dbo.Measurement.SignalReference, dbo.Measurement.Adder, dbo.Measurement.Multiplier, dbo.Measurement.Description, dbo.Measurement.Enabled, 
                      COALESCE (dbo.SignalType.EngineeringUnits, N'') AS EngineeringUnits, dbo.SignalType.Source, dbo.SignalType.Acronym AS SignalAcronym, 
                      dbo.SignalType.Name AS SignalName, dbo.SignalType.Suffix AS SignalTypeSuffix, dbo.Device.Longitude, dbo.Device.Latitude
FROM         dbo.Company RIGHT OUTER JOIN
                      dbo.Device ON dbo.Company.ID = dbo.Device.CompanyID RIGHT OUTER JOIN
                      dbo.Measurement LEFT OUTER JOIN
                      dbo.SignalType ON dbo.Measurement.SignalTypeID = dbo.SignalType.ID ON dbo.Device.ID = dbo.Measurement.DeviceID LEFT OUTER JOIN
                      dbo.Phasor ON dbo.Measurement.DeviceID = dbo.Phasor.DeviceID AND 
                      dbo.Measurement.PhasorSourceIndex = dbo.Phasor.SourceIndex LEFT OUTER JOIN
                      dbo.VendorDevice ON dbo.Device.VendorDeviceID = dbo.VendorDevice.ID LEFT OUTER JOIN
                      dbo.Protocol ON dbo.Device.ProtocolID = dbo.Protocol.ID
GO
/****** Object:  View [dbo].[PhasorDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PhasorDetail]
AS
SELECT     P.ID, P.DeviceID, P.Label, P.Type, P.Phase, P.DestinationPhasorID, P.SourceIndex, P.NetworkModelID, ISNULL(DP.Label, '') 
                      AS DestinationPhasorLabel, D.Acronym AS DeviceAcronym, ISNULL(NM.NetworkElementUID, '') AS NetworkElementUID
FROM         dbo.Phasor AS P LEFT OUTER JOIN
                      dbo.Phasor AS DP ON P.DestinationPhasorID = DP.ID LEFT OUTER JOIN
                      dbo.Device AS D ON P.DeviceID = D.ID LEFT OUTER JOIN
                      dbo.NetworkModel AS NM ON P.NetworkModelID = NM.ID
GO
/****** Object:  View [dbo].[VendorDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VendorDetail] AS
Select ID, ISNULL(Acronym, '') AS Acronym, Name, ISNULL(PhoneNumber, '') AS PhoneNumber, ISNULL(ContactEmail, '') AS ContactEmail, ISNULL(URL, '') AS URL 
FROM Vendor
GO
/****** Object:  View [dbo].[VendorDeviceDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VendorDeviceDetail]
AS
SELECT     VD.ID, VD.VendorID, VD.Name, ISNULL(VD.Description, '') AS Description, ISNULL(VD.URL, '') AS URL, V.Name AS VendorName, 
                      V.Acronym AS VendorAcronym
FROM         dbo.VendorDevice AS VD INNER JOIN
                      dbo.Vendor AS V ON VD.VendorID = V.ID
GO
/****** Object:  View [dbo].[CompanyDetail]    Script Date: 05/12/2010 14:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CompanyDetail]
AS
SELECT     C.ID, C.Acronym, C.MapAcronym, C.Name, ISNULL(C.URL, '') AS URL, C.InterconnectionID, C.NercRegionID, C.TimeZone, 
                      I.Name AS InterconnectionName, N.Name AS NercRegionName
FROM         dbo.Company AS C INNER JOIN
                      dbo.Interconnection AS I ON C.InterconnectionID = I.ID INNER JOIN
                      dbo.NercRegion AS N ON C.NercRegionID = N.ID
GO

/****** Object:  View [dbo].[ContactDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ContactDetail]
AS
SELECT     dbo.Contact.ID, dbo.Contact.CompanyID, dbo.Contact.ContactTypeID, dbo.Contact.FirstName, dbo.Contact.LastName, 
                      dbo.Contact.FirstName + ' ' + dbo.Contact.LastName AS ContactFullName, ISNULL(dbo.Contact.Title, '') AS Title, dbo.Contact.Email, dbo.Contact.Phone, 
                      dbo.Company.Name AS CompanyName, dbo.ContactType.Name AS ContactTypeName
FROM         dbo.Contact INNER JOIN
                      dbo.Company ON dbo.Contact.CompanyID = dbo.Company.ID INNER JOIN
                      dbo.ContactType ON dbo.Contact.ContactTypeID = dbo.ContactType.ID
GO
/****** Object:  View [dbo].[NetworkModelDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[NetworkModelDetail] AS
SELECT ID, NetworkElementUID, LineBusName, RegionalLineBusID, LocalLineBusID, 
	ISNULL(ExtraLineBusID, '') AS ExtraLineBusID, NominalKv
FROM NetworkModel
GO
/****** Object:  View [dbo].[DeviceDetail]    Script Date: 05/12/2010 14:40:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DeviceDetail]
AS
SELECT     D.ID, D.Acronym, D.Name, D.SubstationAcronym, D.SubstationName, D.Longitude, D.Latitude, D.TimeZone, D.InterconnectionID, D.CompanyID, D.VendorDeviceID, 
                      D.ProtocolID, D.MeasuredLines, D.IsConcentrator, D.ParentID, D.StatusID, D.StatusDate, D.TechnicalContactID, D.InServiceDate, D.CreatedOn, D.UpdatedOn, 
                      ISNULL(C.Name, '') AS CompanyName, ISNULL(C.Acronym, '') AS CompanyAcronym, ISNULL(C.MapAcronym, '') AS CompanyMapAcronym, ISNULL(VD.VendorAcronym, 
                      '') AS VendorAcronym, ISNULL(VD.Name, '') AS VendorDeviceName, ISNULL(P.Name, '') AS ProtocolName, ISNULL(I.Name, '') AS InterconnectionName, 
                      ISNULL(I.Acronym, '') AS Interconnection, ISNULL(PD.Acronym, '') AS ParentAcronym, DS.Name AS StatusName, ISNULL(CT.FirstName, '') + ' ' + ISNULL(CT.LastName, 
                      '') AS ContactFullName, D.FundingSourceID, ISNULL(F.SourceName, '') AS FundingSourceName
FROM         dbo.Device AS D LEFT OUTER JOIN
                      dbo.Company AS C ON C.ID = D.CompanyID LEFT OUTER JOIN
                      dbo.VendorDeviceDetail AS VD ON VD.ID = D.VendorDeviceID LEFT OUTER JOIN
                      dbo.Protocol AS P ON P.ID = D.ProtocolID LEFT OUTER JOIN
                      dbo.Interconnection AS I ON I.ID = D.InterconnectionID LEFT OUTER JOIN
                      dbo.Device AS PD ON PD.ID = D.ParentID LEFT OUTER JOIN
                      dbo.DeviceStatus AS DS ON DS.ID = D.StatusID LEFT OUTER JOIN
                      dbo.Contact AS CT ON C.ID = D.TechnicalContactID LEFT OUTER JOIN
                      dbo.FundingSource AS F ON F.ID = D.FundingSourceID
GO
/****** Object:  View [dbo].[DeviceStatistics]    Script Date: 05/26/2010 14:24:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[DeviceStatistics]
AS
SELECT     I.Acronym AS Interconnection, ISNULL(DS.DeviceCount, 0) AS DeviceCount, ISNULL(DS.MeasuredLines, 0) AS MeasuredLines
FROM         dbo.Interconnection AS I LEFT OUTER JOIN
                          (SELECT     Interconnection, COUNT(*) AS DeviceCount, SUM(MeasuredLines) AS MeasuredLines
                            FROM          dbo.DeviceDetail
                            WHERE      (IsConcentrator = '0')
                            GROUP BY Interconnection) AS DS ON I.Acronym = DS.Interconnection

GO
/****** Object:  ForeignKey [FK_Company_Interconnection]    Script Date: 05/12/2010 14:40:04 ******/
ALTER TABLE [dbo].[Company]  WITH CHECK ADD  CONSTRAINT [FK_Company_Interconnection] FOREIGN KEY([InterconnectionID])
REFERENCES [dbo].[Interconnection] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Company] CHECK CONSTRAINT [FK_Company_Interconnection]
GO
/****** Object:  ForeignKey [FK_Company_NercRegion]    Script Date: 05/12/2010 14:40:04 ******/
ALTER TABLE [dbo].[Company]  WITH CHECK ADD  CONSTRAINT [FK_Company_NercRegion] FOREIGN KEY([NercRegionID])
REFERENCES [dbo].[NercRegion] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Company] CHECK CONSTRAINT [FK_Company_NercRegion]
GO
/****** Object:  ForeignKey [FK_Contact_Company]    Script Date: 05/12/2010 14:40:04 ******/
ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Company] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Company] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Company]
GO
/****** Object:  ForeignKey [FK_Contact_ContactType]    Script Date: 05/12/2010 14:40:04 ******/
ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_ContactType] FOREIGN KEY([ContactTypeID])
REFERENCES [dbo].[ContactType] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_ContactType]
GO
/****** Object:  ForeignKey [FK_Device_Company]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Company] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Company] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Company]
GO
/****** Object:  ForeignKey [FK_Device_Contact]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Contact] FOREIGN KEY([TechnicalContactID])
REFERENCES [dbo].[Contact] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Contact]
GO
/****** Object:  ForeignKey [FK_Device_Device]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Device] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Device] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Device]
GO
/****** Object:  ForeignKey [FK_Device_DeviceStatus]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_DeviceStatus] FOREIGN KEY([StatusID])
REFERENCES [dbo].[DeviceStatus] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_DeviceStatus]
GO
/****** Object:  ForeignKey [FK_Device_FundingSource]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_FundingSource] FOREIGN KEY([FundingSourceID])
REFERENCES [dbo].[FundingSource] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_FundingSource]
GO
/****** Object:  ForeignKey [FK_Device_Interconnection]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Interconnection] FOREIGN KEY([InterconnectionID])
REFERENCES [dbo].[Interconnection] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Interconnection]
GO
/****** Object:  ForeignKey [FK_Device_Protocol]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Protocol] FOREIGN KEY([ProtocolID])
REFERENCES [dbo].[Protocol] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Protocol]
GO
/****** Object:  ForeignKey [FK_Device_VendorDevice]    Script Date: 05/12/2010 14:40:05 ******/
ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_VendorDevice] FOREIGN KEY([VendorDeviceID])
REFERENCES [dbo].[VendorDevice] ([ID])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_VendorDevice]
GO
/****** Object:  ForeignKey [FK_Measurement_Device]    Script Date: 05/12/2010 14:40:06 ******/
ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [FK_Measurement_Device] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Device] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_Device]
GO
/****** Object:  ForeignKey [FK_Measurement_SignalType]    Script Date: 05/12/2010 14:40:06 ******/
ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [FK_Measurement_SignalType] FOREIGN KEY([SignalTypeID])
REFERENCES [dbo].[SignalType] ([ID])
GO
ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_SignalType]
GO
/****** Object:  ForeignKey [FK_Phasor_Device]    Script Date: 05/12/2010 14:40:06 ******/
ALTER TABLE [dbo].[Phasor]  WITH CHECK ADD  CONSTRAINT [FK_Phasor_Device] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Device] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Phasor] CHECK CONSTRAINT [FK_Phasor_Device]
GO
/****** Object:  ForeignKey [FK_Phasor_NetworkModel]    Script Date: 05/12/2010 14:40:06 ******/
ALTER TABLE [dbo].[Phasor]  WITH CHECK ADD  CONSTRAINT [FK_Phasor_NetworkModel] FOREIGN KEY([NetworkModelID])
REFERENCES [dbo].[NetworkModel] ([ID])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[Phasor] CHECK CONSTRAINT [FK_Phasor_NetworkModel]
GO
/****** Object:  ForeignKey [FK_Phasor_Phasor]    Script Date: 05/12/2010 14:40:06 ******/
ALTER TABLE [dbo].[Phasor]  WITH CHECK ADD  CONSTRAINT [FK_Phasor_Phasor] FOREIGN KEY([DestinationPhasorID])
REFERENCES [dbo].[Phasor] ([ID])
GO
ALTER TABLE [dbo].[Phasor] CHECK CONSTRAINT [FK_Phasor_Phasor]
GO
/****** Object:  ForeignKey [FK_VendorDevice_Vendor]    Script Date: 05/12/2010 14:40:07 ******/
ALTER TABLE [dbo].[VendorDevice]  WITH CHECK ADD  CONSTRAINT [FK_VendorDevice_Vendor] FOREIGN KEY([VendorID])
REFERENCES [dbo].[Vendor] ([ID])
GO
ALTER TABLE [dbo].[VendorDevice] CHECK CONSTRAINT [FK_VendorDevice_Vendor]
GO
